﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using MySql.Data.MySqlClient;
using Model;

using Utility;

namespace DAL
{
    public class IdeaService
    {
        public Idea GetIdeaByID(int IID, string UID)
        {
            MySqlParameter[] parms = new MySqlParameter[] {
                new MySqlParameter("?IdeaID",IID),
                new MySqlParameter("?UserID",UID)
            };

            Idea idea = new Idea();
            using (MySqlDataReader sdr = MySQLHelper.ExecuteReader("sp_GetIdeaByID", parms))
            {
                if (sdr.Read())
                {
                    idea.IID = IID;
                    idea.Title = sdr["Title"].ToString();
                    idea.Content = sdr["Content"].ToString();
                    idea.Attachments = sdr["Attachments"].ToString();
                    idea.CategoryType = int.Parse(sdr["CategoryType"].ToString());
                    idea.CreatedTime = sdr["CreatedTime"].ToString();
                    idea.Votes = int.Parse(sdr["VoteNum"].ToString());
                    idea.Status = int.Parse(sdr["Status"].ToString());

                    idea.CreatedBy = sdr["CreatedBy"].ToString();
                    idea.FirstName = sdr["FirstName"].ToString();
                    idea.LastName = sdr["LastName"].ToString();
                    idea.MapTypes = sdr["UType"] != DBNull.Value ? sdr["UType"].ToString() : "";
                }
            }

            return idea;
        }

        public IList<Idea> GetIdeaListByAll(string UserID, int pagesize, int pageindex)
        {
            MySqlParameter[] parms = new MySqlParameter[]{
                new MySqlParameter("?UserID",UserID),
                new MySqlParameter("?PageSize",pagesize),
                new MySqlParameter("?PageIndex",pageindex)
            };

            IList<Idea> list = new List<Idea>();
            using (MySqlDataReader sdr = MySQLHelper.ExecuteReader("sp_GetIdeaListByAll", parms))
            {
                while (sdr.Read())
                {
                    Idea idea = new Idea();
                    idea.IID = int.Parse(sdr["IID"].ToString());
                    idea.Title = sdr["Title"].ToString();
                    idea.Content = sdr["Content"].ToString();
                    idea.Attachments = sdr["Attachments"].ToString();
                    idea.CategoryType = int.Parse(sdr["CategoryType"].ToString());
                    idea.CreatedTime = sdr["CreatedTime"].ToString();
                    idea.Votes = int.Parse(sdr["VoteNum"].ToString());
                    idea.Status = int.Parse(sdr["Status"].ToString());

                    idea.CreatedBy = sdr["CreatedBy"].ToString();
                    idea.FirstName = sdr["FirstName"].ToString();
                    idea.LastName = sdr["LastName"].ToString();
                    idea.MapTypes = sdr["UType"] != DBNull.Value ? sdr["UType"].ToString() : "";
                    idea.CommentNum = sdr["Commentnum"] != DBNull.Value ? int.Parse(sdr["Commentnum"].ToString()) : 0;

                    list.Add(idea);
                }
            }
            return list;
        }

        public string GetIdeaListCountByAll()
        {
            return MySQLHelper.ExecuteScalar("sp_GetIdeaListCount", null).ToString();
        }

        public string GetIdeaListCountByUser(string UID)
        {
            MySqlParameter parm = new MySqlParameter("?UserID", UID);
            return MySQLHelper.ExecuteScalar("sp_GetIdeaListCountByUser", parm).ToString();
        }

        public string GetIdeaListCountByFavourit(string UID)
        {
            MySqlParameter parm = new MySqlParameter("?UserID", UID);
            return MySQLHelper.ExecuteScalar("sp_GetIdeaListCountByFavourite", parm).ToString();
        }

        public IList<Idea> GetIdeaListByVotes(string UserID, int pagesize, int pageindex)
        {
            MySqlParameter[] parms = new MySqlParameter[]{
                new MySqlParameter("?UserID",UserID),
                new MySqlParameter("?PageSize",pagesize),
                new MySqlParameter("?PageIndex",pageindex)
            };

            IList<Idea> list = new List<Idea>();
            using (MySqlDataReader sdr = MySQLHelper.ExecuteReader("sp_GetIdeaListByVotes", parms))
            {
                while (sdr.Read())
                {
                    Idea idea = new Idea();
                    idea.IID = int.Parse(sdr["IID"].ToString());
                    idea.Title = sdr["Title"].ToString();
                    idea.Content = sdr["Content"].ToString();
                    idea.Attachments = sdr["Attachments"].ToString();
                    idea.CategoryType = int.Parse(sdr["CategoryType"].ToString());
                    idea.CreatedTime = sdr["CreatedTime"].ToString();
                    idea.Votes = int.Parse(sdr["VoteNum"].ToString());
                    idea.Status = int.Parse(sdr["Status"].ToString());

                    idea.CreatedBy = sdr["CreatedBy"].ToString();
                    idea.FirstName = sdr["FirstName"].ToString();
                    idea.LastName = sdr["LastName"].ToString();
                    idea.MapTypes = sdr["UType"] != DBNull.Value ? sdr["UType"].ToString() : "";
                    idea.CommentNum = sdr["Commentnum"] != DBNull.Value ? int.Parse(sdr["Commentnum"].ToString()) : 0;

                    list.Add(idea);
                }
            }

            return list;
        }

        public IList<Idea> GetIdeaListByUser(string UID, int pagesize, int pageindex)
        {
            MySqlParameter[] parms = new MySqlParameter[]{
                new MySqlParameter("?UserID",UID),
                new MySqlParameter("?PageSize",pagesize),
                new MySqlParameter("?PageIndex",pageindex)
            };

            IList<Idea> list = new List<Idea>();
            using (MySqlDataReader sdr = MySQLHelper.ExecuteReader("sp_GetIdeaListByUser", parms))
            {
                while (sdr.Read())
                {
                    Idea idea = new Idea();
                    idea.IID = int.Parse(sdr["IID"].ToString());
                    idea.Title = sdr["Title"].ToString();
                    idea.Content = sdr["Content"].ToString();
                    idea.Attachments = sdr["Attachments"].ToString();
                    idea.CategoryType = int.Parse(sdr["CategoryType"].ToString());
                    idea.CreatedTime = sdr["CreatedTime"].ToString();
                    idea.Votes = int.Parse(sdr["VoteNum"].ToString());
                    idea.Status = int.Parse(sdr["Status"].ToString());

                    idea.CreatedBy = UID;
                    idea.FirstName = sdr["FirstName"].ToString();
                    idea.LastName = sdr["LastName"].ToString();
                    idea.MapTypes = sdr["UType"] != DBNull.Value ? sdr["UType"].ToString() : "";
                    idea.CommentNum = sdr["Commentnum"] != DBNull.Value ? int.Parse(sdr["Commentnum"].ToString()) : 0;

                    list.Add(idea);
                }
            }

            return list;
        }

        public IList<Idea> GetIdeaListByFavourite(string UID, int pagesize, int pageindex)
        {
            MySqlParameter[] parms = new MySqlParameter[]{
                new MySqlParameter("?UserID",UID),
                new MySqlParameter("?PageSize",pagesize),
                new MySqlParameter("?PageIndex",pageindex)
            };

            IList<Idea> list = new List<Idea>();
            using (MySqlDataReader sdr = MySQLHelper.ExecuteReader("sp_GetIdeaListByFavourite", parms))
            {
                while (sdr.Read())
                {
                    if (sdr["UType"] != DBNull.Value)
                    {
                        Idea idea = new Idea();
                        idea.IID = int.Parse(sdr["IID"].ToString());
                        idea.Title = sdr["Title"].ToString();
                        idea.Content = sdr["Content"].ToString();
                        idea.Attachments = sdr["Attachments"].ToString();
                        idea.CategoryType = int.Parse(sdr["CategoryType"].ToString());
                        idea.CreatedTime = sdr["CreatedTime"].ToString();
                        idea.Votes = int.Parse(sdr["VoteNum"].ToString());
                        idea.Status = int.Parse(sdr["Status"].ToString());

                        idea.CreatedBy = UID;
                        idea.FirstName = sdr["FirstName"].ToString();
                        idea.LastName = sdr["LastName"].ToString();
                        idea.MapTypes = sdr["UType"] != DBNull.Value ? sdr["UType"].ToString() : "";
                        idea.CommentNum = sdr["Commentnum"] != DBNull.Value ? int.Parse(sdr["Commentnum"].ToString()) : 0;

                        list.Add(idea);
                    }
                }
            }

            return list;
        }

        public int CreateIdea(Idea idea)
        {
            MySqlParameter[] parms = new MySqlParameter[] {
                new MySqlParameter("?_Title",idea.Title),
                new MySqlParameter("?_Content",idea.Content),
                new MySqlParameter("?_Attachments",idea.Attachments),
                new MySqlParameter("?_CategoryType",idea.CategoryType),
                new MySqlParameter("?_CreatedBy",idea.CreatedBy),
                new MySqlParameter("?_CreatedTime",idea.CreatedTime),
                new MySqlParameter("?_VoteNum",idea.Votes),
                new MySqlParameter("?_Status",idea.Status)
            };

            return MySQLHelper.ExecuteNonQuery("sp_CreateIdea", parms);

        }

        public int DeleteIdeaByID(int ID)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public int UpdateIdea(Idea idea)
        {
            throw new Exception("The method or operation is not implemented.");
        }

        public int ChangeIdeaStatus(int statusType)
        {
            throw new Exception("The method or operation is not implemented.");
        }
    }
}
